<?php

require_once('./fpdf/tcpdf/tcpdf.php');

// create new PDF document
//$pdf = new TCPDF(PDF_PAGE_ORIENTATION, PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);
$pdf = new TCPDF($orientation = 'L', $unit = 'mm', $format = 'A4', $unicode = true, $encoding = 'UTF-8', $diskcache = false, $pdfa = false);

/*
  // set document information
  $pdf->SetCreator(PDF_CREATOR);
  $pdf->SetAuthor('Nicola Asuni');
  $pdf->SetTitle('TCPDF Example 048');
  $pdf->SetSubject('TCPDF Tutorial');
  $pdf->SetKeywords('TCPDF, PDF, example, test, guide');
 */
// set default header data
//$pdf->SetHeaderData(PDF_HEADER_LOGO, PDF_HEADER_LOGO_WIDTH, PDF_HEADER_TITLE.' 04', PDF_HEADER_STRING);
//$pdf->SetFont('thsarabunb','B',22);
$pdf->SetHeaderData("", "", "รายงานสมุดเงินสดจ่าย", "");

// set header and footer fonts
//$pdf->setHeaderFont(Array(PDF_FONT_NAME_MAIN, '', PDF_FONT_SIZE_MAIN));
$pdf->setHeaderFont(Array("thsarabunb", 'B', 22));

$pdf->setFooterFont(Array(PDF_FONT_NAME_DATA, '', PDF_FONT_SIZE_DATA));

// set default monospaced font
$pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);

// set margins
$pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT);
$pdf->SetHeaderMargin(PDF_MARGIN_HEADER);
$pdf->SetFooterMargin(PDF_MARGIN_FOOTER);

// set auto page breaks
$pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);

// set image scale factor
//$pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);
// set some language-dependent strings (optional)
// ---------------------------------------------------------
// set font
//$pdf->SetFont('helvetica', 'B', 20);
$pdf->SetFont('thsarabunb', 'B', 22);

// add a page
$pdf->AddPage();

//$pdf->Write(0, 'รายงานสมุดเงินสดจ่าย', '', 0, 'L', true, 0, false, false, 0);
//$pdf->SetFont('helvetica', '', 8);
// -----------------------------------------------------------------------------


$pdf->SetFont('thsarabunb', 'B', 16);

$tbl_header = '
<table cellspacing="0" cellpadding="1" border="1" width="100%">

';

require_once("includes/connect.php");
require_once("includes/function.php");

$from = $_REQUEST["from"];
$to = $_REQUEST["to"];
$bank_name = $_REQUEST["bank_name"];
$branch_name = $_REQUEST["branch_name"];
$ex1_id = $_REQUEST["ex1_id"];


$db = new myDBS();
$db->selectDB("account");
//
$sql = "SELECT expenses2_id, expenses_lvl2.expenses2_name AS expenses2_name, expenses_lvl2.e_id AS e_id "
        . " FROM tbl_salary, expenses_lvl2 "
        . " WHERE expenses_lvl2.expenses2_id = tbl_salary.ex2_id "
        . " AND tbl_salary.approved_status =  'อนุมัติ' "
        . " AND date_create BETWEEN '$from' AND '$to' "
        . " UNION "
        . " SELECT expenses2_id, expenses_lvl2.expenses2_name AS expenses2_name, expenses_lvl2.e_id AS e_id "
        . " FROM tbl_pet, expenses_lvl2"
        . " WHERE expenses_lvl2.expenses2_id = tbl_pet.ex2_id "
        . " AND tbl_pet.approved_status =  'อนุมัติ' "
        . " AND date_create BETWEEN '$from' AND '$to'"
        . " UNION "
        . " SELECT expenses2_id, expenses_lvl2.expenses2_name AS expenses2_name, expenses_lvl2.e_id AS e_id "
        . " FROM tbl_welfare, expenses_lvl2 "
        . " WHERE expenses_lvl2.expenses2_id = tbl_welfare.ex2_id "
        . " AND tbl_welfare.approved_status =  'อนุมัติ' "
        . " AND date_create BETWEEN '$from' AND '$to' "
        . " UNION "
        . " SELECT DISTINCT expenses2_id, expenses_lvl2.expenses2_name, expenses_lvl2.e_id AS e_id "
        . " FROM tbl_petition, expenses_lvl2 "
        . " WHERE expenses_lvl2.expenses2_id = tbl_petition.ex2_id "
        . " AND tbl_petition.approved_status =  'อนุมัติ' "
        . " AND date_create BETWEEN '$from' AND '$to' ";


//echo $sql;
$result = $db->runSQL($sql);

while ($rows = $db->fetch_object($result)) {
    $tbl_body = $tbl_body . '
        <tr>
            <td align="left" colspan="7">' . $rows->e_id . '  ' . $rows->expenses2_name . '</td>
        </tr>

        ';

    $sql = "SELECT date_create, expenses_lvl3.expenses3_name AS expenses3_name, bank_name, tbl_salary.salary AS net, tax1 "
            . " FROM tbl_salary "
            . " LEFT JOIN expenses_lvl3 ON expenses_lvl3.expenses3_id = tbl_salary.ex3_id "
            . " JOIN tbl_init ON tbl_init.card_id = tbl_salary.id "
            . " WHERE  "
            . " tbl_salary.approved_status =  'อนุมัติ' "
            . " AND date_create BETWEEN '$from' AND '$to' "
            . " AND ex2_id = '$rows->expenses2_id' "
            . " UNION "
            . " SELECT date_create, expenses_lvl3.expenses3_name AS expenses3_name, bank_name, net, tax1 "
            . " FROM tbl_pet "
            . " LEFT JOIN expenses_lvl3 ON expenses_lvl3.expenses3_id = tbl_pet.ex3_id "
            . " WHERE "
            . " tbl_pet.approved_status =  'อนุมัติ' "
            . " AND date_create BETWEEN '$from' AND '$to' "
            . " AND ex2_id = '$rows->expenses2_id' "
            . " UNION "
            . " SELECT date_create, expenses_lvl3.expenses3_name AS expenses3_name, bank_name, tbl_welfare.welfare AS net, tax1 "
            . " FROM tbl_welfare "
            . " LEFT JOIN expenses_lvl3 ON expenses_lvl3.expenses3_id = tbl_welfare.ex3_id "
            . " JOIN tbl_init ON tbl_init.card_id = tbl_welfare.id "
            . " WHERE "
            . " tbl_welfare.approved_status =  'อนุมัติ' "
            . " AND date_create BETWEEN '$from' AND '$to' "
            . " AND ex2_id = '$rows->expenses2_id' "
            . " UNION "
            . " SELECT date_create, expenses_lvl3.expenses3_name AS expenses3_name, bank_name, inpNet AS net, (inpValues * WithholdingTax) / 100 AS tax1 "
            . " FROM tbl_petition "
            . " LEFT JOIN expenses_lvl3 ON expenses_lvl3.expenses3_id = tbl_petition.ex3_id "
            . " WHERE "
            . " tbl_petition.approved_status =  'อนุมัติ' "
            . " AND date_create BETWEEN '$from' AND '$to' "
            . " AND ex2_id = '$rows->expenses2_id' ";
//    $rows->expenses2_id
//    $tbl_body = $tbl_body . '
//        <tr>
//            <td align="left">' . $sql . '</td>
//        </tr>
//
//        ';
    $result1 = $db->runSQL($sql);
    $tbl_body = $tbl_body . '
            <tr align="center">
                <td width="2%">#</td>
                <td width="10%">วันที่</td>
                <td width="35%">รายการ</td>
                <td width="15%">ธนาคาร</td>
                <td width="8%">เลขที่เช็ค</td>
                <td width="10%">หัก ณ ที่จ่าย</td>
                <td width="20%">จำนวนเงิน</td>
            </tr>
            ';
    while ( $rows1 = $db->fetch_object ( $result1 )){
        $date = date_create($rows1->date_create);
        $tbl_body = $tbl_body . '
                    <tr>
                        <td align="center">' . $j . '</td>
                        <td align="center">' . date_format($date, "d/m/Y") . '</td>
                        <td>' . $rows1->expenses3_name . '</td>
                        <td align="center">' . $rows1->bank_name . '</td>
                        <td align="center">' .   '&nbsp;</td>
                        <td align="right">' . number_format($rows1->tax1, 2) .'</td>
                        <td align="right">' . number_format($rows1->net, 2) . '</td>
                    </tr>
                    ';
    }
    
}

$tbl_footer = '</table>';

$pdf->writeHTML($tbl_header . $tbl_body . $tbl_footer, true, false, false, false, '');
//
////if ($db->num_rows($result) == 0) {
////    $tbl_body = '
////		<tr>
////			<td align="left">ไม่พบข้อมูลที่ต้องการค้นหา</td>
////		</tr>
////		
////		';
////}
//
////$pdf->SetFont('thsarabunb', '', 16);
////$i = 0;
////while ($rows = $db->fetch_object($result)) {
////    $i++;
////
////    /*
////      $sql ="SELECT * ".
////      "FROM tbl_petition ".
////      "WHERE ex1_id = '" .$rows->ex1_id. "'";
////     */
////
//////    $sql = "SELECT  expenses2_name ,  bank_name ,  branch_name ,  cheque_date ,  cheque_no, sum(salary) as salary 
//////			FROM tbl_salary, expenses_lvl2
//////			WHERE ex1_id = '" . $rows->ex1_id . "'
//////			AND cheque_status =  'อนุมัติ'
//////			AND ex2_id = expenses2_id
//////			GROUP BY expenses2_name, bank_name, branch_name, cheque_date, cheque_no
//////			UNION
//////			SELECT  expenses2_name ,  bank_name ,  branch_name ,  cheque_date ,  cheque_no, utility 
//////			FROM tbl_utility, expenses_lvl2
//////			WHERE ex1_id = '" . $rows->ex1_id . "'
//////			AND cheque_status =  'อนุมัติ'
//////			AND ex2_id = expenses2_id
//////			UNION
//////			SELECT  expenses2_name ,  bank_name ,  branch_name ,  cheque_date ,  cheque_no, sum(welfare) as salary 
//////			FROM tbl_welfare, expenses_lvl2
//////			WHERE ex1_id = '" . $rows->ex1_id . "'
//////			AND cheque_status =  'อนุมัติ'
//////			AND ex2_id = expenses2_id
//////			GROUP BY expenses2_name, bank_name, branch_name, cheque_date, cheque_no
//////			UNION 
//////			SELECT company_name,  bank_name ,  branch_name ,  cheque_date ,  cheque_no, inpNet 
//////			FROM tbl_petition
//////			WHERE ex1_id = '" . $rows->ex1_id . "'
//////			AND cheque_status =  'Approved'
//////		";
//////    $result1 = $db->runSQL($sql);
////
////
////    $tbl_body = $tbl_body . '
////		<tr>
////			<td colspan="7" align="left">' . $rows->expenses2_name . '  ' . $rows->expenses2_id . '</td>
////		</tr>
////		
////		';
////    //while ( $rows1 = $db->fetch_object ( $result1 )){
////    //}
////
////    $j = 1;
////    $num_rows = $db->num_rows($result1);
////    $sum = 0;
////    $tbl_body = $tbl_body . '
////		<tr align="center">
////			<td width="2%">#</td>
////			<td width="10%">วันที่</td>
////			<td width="35%">รายการ</td>
////			<td width="15%">ธนาคาร</td>
////			<td width="8%">เลขที่เช็ค</td>
////			<td width="10%">หัก ณ ที่จ่าย</td>
////			<td width="20%">จำนวนเงิน</td>
////		</tr>
////		';
////
//////    while ($rows1 = $db->fetch_object($result1)) {
//////        $date = date_create($rows1->cheque_date);
//////        //$xxx=11111111111;
//////        //$wTax = $rows1->inpValues * $rows1->WithholdingTax / 100;
//////        $tbl_body = $tbl_body . '
//////				<tr>
//////					<td align="center">' . $j . '</td>
//////					<td align="center">' . date_format($date, "d/m/Y") . '</td>
//////					<td>' . $rows1->expenses2_name . '</td>
//////					<td align="center">' . $rows1->bank_name . '</td>
//////					<td align="center">' . $rows1->cheque_no . '</td>
//////					<td align="right">&nbsp;</td>
//////					<td align="right">' . number_format($rows1->salary, 2) . '</td>
//////				</tr>
//////				';
//////
//////        $sum = $sum + $rows1->salary;
//////        if ($j == $num_rows) {
//////            $tbl_body = $tbl_body . '
//////						<tr>
//////							<td colspan="6" align="right">รวม</td>
//////							<td align="right">' . number_format($sum, 2) . '</td>
//////							
//////						</tr>	
//////					';
//////        }
//////        $j++;
//////    }
////}
////
//$pdf->Output('example_048.pdf', 'I');
$pdf->Output();
?>